![Snowflakeストレージ統合でS3からデータをロードしてみた](https://devio2023-media.developers.io/wp-content/uploads/2021/03/snowflake-logo-1200x630-1.png)
Snowflakeストレージ統合でS3からデータをロードしてみた
SnowflakeではS3のデータロードが可能です。今回は「ストレージ統合」を利用してS3からデータをロードしてみたいと思います。本ブログの手順等は以下ドキュメントを参考にしています。
Snowflakeではフリートライアルが提供されていますので、お試しされる方は以下を参考にしてください。
Snowflake側の操作(その1)
ワークシート(Webインタフェース)または、SnowSQL(CLIクライアント)を使用して、データベースの操作など、クエリを実行することが可能です。
ここでは、SnowSQLを利用しますが、セットアップ等については割愛していますので、SnowSQLを利用されるかたは、以下を参考にしてください。
ストレージ統合の作成
S3等のクラウドストレージにアクセスするオブジェクト「ストレージ統合」を作成します。
ストレージ統合を利用することで、SnowflakeからS3へのアクセスにて永続的なクレデンシャルの使用を回避できます。S3に対するアクセスはこちらの方法が推奨されています。
CREATE STORAGE INTEGRATIONコマンドでストレージ統合を作成します。 ここでは、「test_integration」という名前で作成しています。
CREATE STORAGE INTEGRATION test_integration TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = S3 ENABLED = TRUE STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::0:role/role_name' STORAGE_ALLOWED_LOCATIONS = ('s3://') ;
STORAGE_AWS_ROLE_ARN
、STORAGE_ALLOWED_LOCATIONS
の値はダミーです。
データファイルを格納したS3のパス、そのS3へアクセス可能なIAMロールの情報が必要ですが、後ほど作成します。上記ダミーのままストレージ統合を作成しました。
正常に作成できると、以下のような結果が返ってきます。
+----------------------------------------------------+ | status | |----------------------------------------------------| | Integration TEST_INTEGRATION successfully created. | +----------------------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.180s
DESCRIBE INTEGRATIONコマンドで設定内容を確認します。
>DESC INTEGRATION test_integration; +---------------------------+---------------+------------------------------------------------+------------------+ | property | property_type | property_value | property_default | |---------------------------+---------------+------------------------------------------------+------------------| | ENABLED | Boolean | true | false | | STORAGE_PROVIDER | String | S3 | | | STORAGE_ALLOWED_LOCATIONS | List | s3:// | [] | | STORAGE_BLOCKED_LOCATIONS | List | | [] | | STORAGE_AWS_IAM_USER_ARN | String | arn:aws:iam::555555xxxxxx:user/0pgj-s-jpss8681 | | | STORAGE_AWS_ROLE_ARN | String | arn:aws:iam::0:role/role_name | | | STORAGE_AWS_EXTERNAL_ID | String | 1111111_SFCRole=3_x2Oanh2e9ZLr+cK0fwV7EhwZMTw= | | | COMMENT | String | | | +---------------------------+---------------+------------------------------------------------+------------------+ 8 Row(s) produced. Time Elapsed: 1.112s
STORAGE_AWS_IAM_USER_ARN
、STORAGE_AWS_EXTERNAL_ID
の値を控えておきます。この後のAWS側の操作でIAMロール作成時に利用します。(値はマスクしてます)
AWS側の操作
ここからは、自身のAWSアカウント上の操作となります。
- データファイルが格納されたS3バケットにアクセス可能なIAMポリシーの作成
- Snowflake側のIAMユーザーを信頼関係に設定したIAMロール
- 作成したポリシーをアタッチ
上記、IAMポリシー、IAMロールを作成するCFnテンプレートを用意しました。
template.yml
AWSTemplateFormatVersion: 2010-09-09 Description: Create a role for Snowflake. Parameters: S3BucketName: Type: String Description: Name of the S3 bucket containing the data file. SnowflakeUserArn: Type: String Description: IAM user created in Snowflake's AWS account. SnowflakeExternalId: Type: String Description: External ID to establish trust. Resources: SnowflakeS3AccessRole: Type: AWS::IAM::Role Properties: RoleName: test-snowflake-role Path: / AssumeRolePolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Principal: AWS: !Sub ${SnowflakeUserArn} Action: sts:AssumeRole Condition: StringEquals: sts:ExternalId: !Sub ${SnowflakeExternalId} ManagedPolicyArns: - !Ref SnowflakeS3AccessPolicy SnowflakeS3AccessPolicy: Type: AWS::IAM::ManagedPolicy Properties: ManagedPolicyName: test-snowflake-policy Path: / PolicyDocument: Version: 2012-10-17 Statement: - Effect: Allow Action: - s3:PutObject - s3:GetObject - s3:GetObjectVersion - s3:DeleteObject - s3:DeleteObjectVersion Resource: !Sub arn:aws:s3:::${S3BucketName}/* - Effect: Allow Action: - s3:ListBucket - s3:GetBucketLocation Resource: !Sub arn:aws:s3:::${S3BucketName} Outputs: SnowflakeS3AccessRoleArn: Value: !GetAtt SnowflakeS3AccessRole.Arn
CFnスタックを作成します。
> aws cloudformation deploy \ --stack-name test-snowflake-iam \ --template-file ./template.yml \ --capabilities CAPABILITY_NAMED_IAM \ --parameter-overrides \ S3BucketName=test-snowflake-datafile \ SnowflakeUserArn=arn:aws:iam::555555xxxxxx:user/0pgj-s-jpss8681 \ SnowflakeExternalId=1111111_SFCRole=3_x2Oanh2e9ZLr+cK0fwV7EhwZMTw= Waiting for changeset to be created.. Waiting for stack create/update to complete Successfully created/updated stack - test-snowflake-iam
スタックの作成が完了したら、作成されたIAMロールのARNを確認します。この値でストレージ統合の値を変更します。(値はマスクしてます)
> aws cloudformation describe-stacks \ --stack-name test-snowflake-iam \ --query "Stacks[].Outputs[].OutputValue" \ --output table -------------------------------------------------------- | DescribeStacks | +------------------------------------------------------+ | arn:aws:iam::77777xxxxxx:role/test-snowflake-role | +------------------------------------------------------+
なお、ロードするデータファイルが格納されたS3バケットは既に存在していることにしています。
toshima,nanamaru nazo,fukuro somefuku,chan somei,yoshino
Snowflake側の操作(その2)
ストレージ統合更新
Snowflakeのストレージ統合のSTORAGE_AWS_ROLE_ARN
、STORAGE_ALLOWED_LOCATIONS
を更新します。ALTER STORAGE INTEGRATIONコマンドを実行します。
ALTER STORAGE INTEGRATION test_integration SET STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::77777xxxxxx:role/test-snowflake-role'; ALTER STORAGE INTEGRATION test_integration SET STORAGE_ALLOWED_LOCATIONS = ('s3://test-snowflake-datafile/');
修正内容を確認します。
>DESC INTEGRATION test_integration; +---------------------------+---------------+----------------------------------------------------+------------------+ | property | property_type | property_value | property_default | |---------------------------+---------------+----------------------------------------------------+------------------| | ENABLED | Boolean | true | false | | STORAGE_PROVIDER | String | S3 | | | STORAGE_ALLOWED_LOCATIONS | List | s3://test-snowflake-datafile/ | [] | | STORAGE_BLOCKED_LOCATIONS | List | | [] | | STORAGE_AWS_IAM_USER_ARN | String | arn:aws:iam::555555xxxxxx:user/0pgj-s-jpss8681 | | | STORAGE_AWS_ROLE_ARN | String | arn:aws:iam::77777xxxxxx:role/test-snowflake-role | | | STORAGE_AWS_EXTERNAL_ID | String | 1111111_SFCRole=3_x2Oanh2e9ZLr+cK0fwV7EhwZMTw= | | | COMMENT | String | | | +---------------------------+---------------+----------------------------------------------------+------------------+ 8 Row(s) produced. Time Elapsed: 0.809s
データベース、テーブル作成
S3データをロードする、データベースとテーブルを作成します。CREATE DATABASE、CREATE TABLEコマンドを使用します。
create database testdb; create table testtable (first VARCHAR,last VARCHAR);
外部ステージ作成
データをロードする際のファイル置き場となるステージを作成します。データファイルはクラウド(S3バケット)にありますので、外部ステージを作成します。
CREATE STAGEコマンドで作成します。
CREATE STAGE test_s3_stage STORAGE_INTEGRATION = test_integration URL = 's3://test-snowflake-datafile/';
データロード
データをロードするには、仮想ウェアハウスが必要です。
ここではデフォルトで作成されているCOMPUTE_WH
を使用します。SHOW WAREHOUSESコマンドで状態を確認します。
>show WAREHOUSES; +------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+ | name | state | type | size | min_cluster_count | max_cluster_count | started_clusters | running | queued | is_default | is_current | auto_suspend | auto_resume | available | provisioning | quiescing | other | created_on | resumed_on | updated_on | owner | comment | resource_monitor | actives | pendings | failed | suspended | uuid | scaling_policy | |------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------| | COMPUTE_WH | SUSPENDED | STANDARD | X-Small | 1 | 1 | 0 | 0 | 0 | Y | Y | 600 | true | | | | | 2022-07-04 17:19:04.140 -0700 | 2022-07-06 08:46:33.378 -0700 | 2022-07-06 08:46:33.378 -0700 | SYSADMIN | | null | 0 | 0 | 0 | 1 | 8122628 | STANDARD | +------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+ 1 Row(s) produced. Time Elapsed: 0.130s
停止状態であればALTER WAREHOUSEで起動します。
>alter warehouse COMPUTE_WH resume; +----------------------------------+ | status | |----------------------------------| | Statement executed successfully. | +----------------------------------+ 1 Row(s) produced. Time Elapsed: 0.298s >show WAREHOUSES; +------------+---------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+ | name | state | type | size | min_cluster_count | max_cluster_count | started_clusters | running | queued | is_default | is_current | auto_suspend | auto_resume | available | provisioning | quiescing | other | created_on | resumed_on | updated_on | owner | comment | resource_monitor | actives | pendings | failed | suspended | uuid | scaling_policy | |------------+---------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------| | COMPUTE_WH | STARTED | STANDARD | X-Small | 1 | 1 | 1 | 0 | 0 | Y | Y | 600 | true | 100 | 0 | 0 | 0 | 2022-07-04 17:19:04.140 -0700 | 2022-07-06 21:20:55.731 -0700 | 2022-07-06 21:20:55.731 -0700 | SYSADMIN | | null | 1 | 0 | 0 | 0 | 8122628 | STANDARD | +------------+---------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+ 1 Row(s) produced. Time Elapsed: 0.172s
COPY INTOコマンドでデータをロードします。
>copy into testtable from @test_s3_stage pattern='testdata.csv'; +-------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ | file | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name | |-------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------| | s3://test-snowflake-datafile/testdata.csv | LOADED | 4 | 4 | 1 | 0 | NULL | NULL | NULL | NULL | +-------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ 1 Row(s) produced. Time Elapsed: 3.511s
ロードしたデータを確認します。
>select * from testtable; +----------+----------+ | FIRST | LAST | |----------+----------| | toshima | nanamaru | | nazo | fukuro | | somefuku | chan | | somei | yoshino | +----------+----------+ 4 Row(s) produced. Time Elapsed: 0.321s
さいごに
S3のデータロードについては、冒頭であげたドキュメント等ありますが、少々読み取れない部分があったので実際にやってみてました。誰かのお役に立てれば幸いです。